#!/bin/bash
source /etc/profile

dt=`mysql -uroot -proot -e 'use result;select max(evaluation_time) from res_evaluation'|awk -F '\t' 'NR!=1 {print $1}'`
#echo $dt
mysql -uroot -proot -hhadoop2<<EOF
use oracleanditem;
drop TEMPORARY TABLE if EXISTS MID_EVALUATION;
CREATE TEMPORARY table MID_EVALUATION
select EVALUATION_ID evaluation_id,
sum(case when PROBLEM_ID = 100021 then RESULT else 0 end) hello,
sum(case when PROBLEM_ID = 100022 THEN RESULT else 0 end) clean,
sum(case when PROBLEM_ID = 100023 THEN RESULT else 0 end) sale,
sum(case when PROBLEM_ID = 100020 THEN RESULT else 0 end) goodbye,
sum(case when PROBLEM_ID = 100024 THEN RESULT else 0 end) toilet
from TBL_EVALUATIONRELPROBLEM 
group by EVALUATION_ID; 

select B.OLDUNIQUEID,C.MOBILEPHONE,A.TIME,
case when D.hello = 0 then '否' 
     when D.hello = 1 then '是'
     when D.hello = 2 then '未填写'
else D.hello end,
case when D.clean = 0 then '否' 
     when D.clean = 1 then '是'
     when D.clean = 2 then '未填写'
else D.clean end,
case when D.sale = 0 then '否' 
     when D.sale = 1 then '是'
     when D.sale = 2 then '未填写'
else D.sale end,
case when D.goodbye = 0 then '否' 
     when D.goodbye = 1 then '是'
     when D.goodbye = 2 then '未填写'
else goodbye end,
case when D.toilet = 0 then '否' 
     when D.toilet = 1 then '是'
     when D.toilet = 2 then '未填写'
else D.toilet end,
A.STAR4,A.STAR3,A.STAR,A.CONTENT
from 
(SELECT * from TBL_EVALUATION where TIME>'$dt') AS A 
join TBL_ORGANIZATION AS B 
ON B.ID = A.ORG_ID
JOIN TBL_CUSTOMER AS C
ON A.customer_id = C.customer_id 
JOIN MID_EVALUATION AS D
ON D.EVALUATION_ID = A.EVALUATION_ID;
EOF
